import tkinter as tk
from tkinter import scrolledtext
import pymssql


def open(s):
    window = tk.Tk()
    window.title("系统管理人员(查询)-" + s)
    window.geometry("500x300")

    # 设置列的权重
    window.grid_columnconfigure(0, weight=1)
    window.grid_columnconfigure(1, weight=2)
    window.grid_columnconfigure(2, weight=1)
    label11 = tk.Label(window, text="请输入要查询的信息，")
    label12 = tk.Label(window, text="点击相应按钮进行查询")
    label2 = tk.Label(window, text="输入")
    entry2 = tk.Entry(window)

    conn = pymssql.connect(host='localhost', server='LAPTOP-QBHKFR9T', port='1433', user='Mysql',
                           password='15681743536',
                           database='植物管理系统', charset='utf8')

    if conn:
        print("连接成功！")

    def check_credentials1():
        message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT 分类.编号,分类.名称,省名称,市名称,县名称,生长环境 FROM 分类, 生长环境,分布,分布区域 WHERE 分类.编号 = 生长环境.植物编号 AND 分布.县编号 = 分布区域.县编号 AND 分类.编号 = '{message}'"

        cursor.execute(sql)

        result = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该植物信息"
        else:
            for row in cursor:  # 对每一行数据遍历输出

                result += "ID：" + "%s" % (row[0]) + "  " + "名称:" + "%s" % (row[1]) + "  " + "省份:" + "%s" % (row[2]) + "  " + "市:" + "%s" % (
                              row[3]) + "  " + "县:" + "%s" % (row[4]) + "  " + "生长环境:" + "%s" % (row[5])
            # result += "%-5s,%-4s,%s,%s,%s,%s,%s" % (row[0], row[1], '/'.join(numbers), row[3], row[4], row[5], row[6])

        scrollbar.insert(tk.INSERT, result)

    def check_credentials2():
        message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT 分类.编号,分类.名称,path,省名称,市名称,县名称,生长环境 FROM 分类, 生长环境,分布,分布区域 WHERE 分类.编号 = 生长环境.植物编号 AND 分布.县编号 = 分布区域.县编号 AND 分类.名称 = '{message}'"

        cursor.execute(sql)

        result = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该植物信息"
        else:
            for row in cursor:  # 对每一行数据遍历输出
                numbers = row[2].split('/')
                result += "ID：" + "%s" % (row[0]) + "  " + "名称:" + "%s" % (row[1]) + "  " + "路径:" + "%s" % (
                    '/'.join(numbers)) + "  " + "省份:" + "%s" % (row[3]) + "  " + "市:" + "%s" % (
                              row[4]) + "  " + "县:" + "%s" % (row[5]) + "  " + "县ID:" + "%s" % (row[6])

        scrollbar.insert(tk.INSERT, result)

    def check_credentials3():
        message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT path FROM 分类 WHERE 分类.名称= '{message}'"

        cursor.execute(sql)
        result1 = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该植物信息"
        else:
            for row in cursor:  # 对每一行数据遍历输出
                # numbers = row[2].split('/')

                result1 += "%s" % (row[0])
            print(result1)
            cursor = conn.cursor()  # 获取游标
            sql = f"SELECT DISTINCT 分类.编号, 分类.名称, path FROM 分类 WHERE 分类.path  like CONCAT('{result1}', '%')"

            cursor.execute(sql)

            result = ""
            for row in cursor:  # 对每一行数据遍历输出
                numbers = row[2].split('/')
                result += "ID：" + "%s" % (row[0]) + "    " + "名称:" + "%s" % (row[1]) + "    " + "路径:" + "%s" % (
                    '/'.join(numbers))
                # result += "%s,%s,%s" % (row[0], row[1], '/'.join(numbers))
                result += "\n"

        scrollbar.insert(tk.INSERT, result)

    def check_credentials4():
        message = entry2.get()

        cursor = conn.cursor()  # 获取游标
        sql = f"SELECT DISTINCT * FROM 生长环境 WHERE 生长环境 like CONCAT('%','{message}', '%')"

        cursor.execute(sql)
        result = ""
        if cursor.rowcount == 0:  # 如果没有找到记录
            result = "无该植物信息"
        else:
            for row in cursor:  # 对每一行数据遍历输出
                # numbers = row[2].split('/')
                result += "ID：" + "%s" % (row[0]) + "生长环境:" + "%s" % (
                    row[1])
                # result += "%s,%s,%s" % (row[0], row[1], row[2])
                result += "\n"

        scrollbar.insert(tk.INSERT, result)

    button1 = tk.Button(window, text="按ID查询", command=lambda: check_credentials1(), width=20, height=1)
    button2 = tk.Button(window, text="按植物名称查询", command=lambda: check_credentials2(), width=20,
                        height=1)  # 得到科，属，种，生长环境，分布区域
    button3 = tk.Button(window, text="查询科属种下属所有植物", command=lambda: check_credentials3(), width=20,
                        height=1)  # 仅查询得到植物情况即可
    button6 = tk.Button(window, text="根据生长环境进行模糊查询", command=lambda: check_credentials4(), width=20,
                        height=1)  # 查询出对应植物

    back_button = tk.Button(window, text="返回上一级", command=window.destroy, width=20, height=1)

    label11.grid(row=0, column=0)
    label12.grid(row=0, column=1)
    label2.grid(row=1, column=0)
    entry2.grid(row=1, column=1)
    button1.grid(row=2, column=0)
    button2.grid(row=2, column=1)
    button3.grid(row=2, column=2)
    button6.grid(row=3, column=0)
    back_button.grid(row=3, column=1)

    scrollbar = scrolledtext.ScrolledText(window, wrap=tk.WORD, width=60, height=10)
    scrollbar.grid(row=5, column=0, columnspan=4, sticky="nsew")
    window.mainloop()
    pass
